package br.com.equipe1.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.tomcat.util.codec.binary.StringUtils;

import br.com.equipe1.modelo.Representante;

public class RepresentanteDAO implements CrudDAO<Representante>{
	
	public int inserirCodigoEmpresa(Representante representante) throws SQLException {
		String sql = "INSERT INTO REPRESENTANTE (ENDR_CODIGO, CARG_CODIGO ,REP_NOME ,REP_APELIDO, REP_DT_NASCIMENTO, REP_EMAIL)"
		             + " VALUES (?,?,?,?,?,?)";
		int enderecoCodigo = new EnderecoRepresentanteDAO().inserir(representante.getEndereco());
		int cargoCodigo = new CargoDAO().inserir(representante.getCargo());
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = Conexao.criarConexao();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setInt(1, enderecoCodigo);
			pstm.setInt(2, cargoCodigo);
			pstm.setString(3, representante.getNome());
			pstm.setString(4, representante.getApelido());
			pstm.setDate(5, (Date)representante.getDataNascimento());
			pstm.setString(6, representante.getEmail());
			ResultSet rs = pstm.getGeneratedKeys();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (Exception e) {
			conn.rollback();
			
		} finally {
			
			try {
				if(conn != null){
					conn.close();
				} 
				if(pstm != null){
					pstm.close();
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public void deletar(Representante representante) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void atualizar(Representante representante) throws SQLException {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void inserir(Representante representante) throws SQLException {
		String sql = "INSERT INTO REPRESENTANTE (ENDR_CODIGO, CARG_CODIGO ,REP_NOME ,REP_APELIDO, REP_DT_NASCIMENTO, REP_EMAIL)"
	             + " VALUES (?,?,?,?,?,?)";
		int enderecoCodigo = new EnderecoRepresentanteDAO().inserir(representante.getEndereco());
		int cargoCodigo = new CargoDAO().inserir(representante.getCargo());
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = Conexao.criarConexao();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setInt(1, enderecoCodigo);
			pstm.setInt(2, cargoCodigo);
			pstm.setString(3, representante.getNome());
			pstm.setString(4, representante.getApelido());
			pstm.setDate(5, (Date)representante.getDataNascimento());
			pstm.setString(6, representante.getEmail());
			
		} catch (Exception e) {
			conn.rollback();
			
		} finally {
			
			try {
				if(conn != null){
					conn.close();
				} 
				if(pstm != null){
					pstm.close();
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
	}
	
	public List<Representante> buscarPorFiltro(Map<String, Object> filter){
		String nome = filter.get("nome").toString();
		Date dataDeNascimento =  (Date) filter.get("dataDeNascimento");
		String email = filter.get("email").toString();
		
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT * FROM REPRESENTANTE rep ");
		sql.append(" INNER JOIN CARGO CAR ON CAR.CARG_CODIGO = REP.CARG_CODIGO ");
		sql.append(" INNER JOIN ENDERECO_REPRESENTANTE ENDREP ON ENDREP.ENDR_CODIGO = REP.ENDR_CODIGO ");
//		sql.append(" WHERE 1 = 1 ");
//		sql.append(!isNomeNulo(filter) ? " AND UPPER (REP.REP_NOME) LIKE UPPER (?) " : "");
//		sql.append(!isDataDeNascimentoNulo(filter) ? " AND REP.REP_DT_NASCIMENTO = ? " : "");
//		sql.append(!isEmailNulo(filter) ? " AND UPPER(REP.REP_EMAIL) LIKE UPPER(?) " : "");
		sql.append(" WHERE UPPER (REP.REP_NOME) LIKE UPPER (?) " );
		sql.append(" AND REP.REP_DT_NASCIMENTO = ? ");
		sql.append(" AND UPPER(REP.REP_EMAIL) LIKE UPPER(?) ");
		sql.append(" ORDER BY REP.REP_NOME ");
				
		Connection conn = null;
		PreparedStatement pstm = null;
		List<Representante> representantes = new ArrayList<>();		
		try {
			conn = Conexao.criarConexao();
			pstm = conn.prepareStatement(sql.toString());
			pstm.setString(1, "%" + nome + "%");
			pstm.setDate(2, dataDeNascimento);
			pstm.setString(3, email);
			ResultSet rs = pstm.executeQuery();
			if(rs.next()){
				representantes.add(popularRepresentante(rs));
			}
			
		} catch (Exception e) {
			
		}
	}
	
	private Representante popularRepresentante(ResultSet rs) throws SQLException {
		Representante representante = new Representante();
		representante.setNome(rs.getString("nome"));
		return null;
	}

//	private boolean isNomeNulo(Map<String, Object> filter){
//		return filter.get("nome").toString() == null;
//	}
//	
//	private boolean isDataDeNascimentoNulo(Map<String, Object> filter){
//		return filter.get("dataDeNascimento") == null;
//	}
//	
//	private boolean isEmailNulo(Map<String, Object> filter){
//		return filter.get("email") == null; 
//	}
}
